"""
Copyright (c) 2023 - present SKbioinfo
"""

from apps.NGS_API.connect import MysqlConnection
from apps.NGS_API.common.db_table_config import *

def get_sample_info():
    ### set up MYSQL connection ###
    db_conn=MysqlConnection()
    ### SQL: total number of samples ###
    samples_total_number_sql = '''
    SELECT 
        COUNT(DISTINCT sample_id)
    FROM sms_sample
    '''
    samples_total_number = (db_conn.Query(samples_total_number_sql))[0]
    ### SQL: sample_base_info ###
    sample_base_info_sql = '''
    SELECT 
        tb1.sample_id,
        tb1.project_id,
        tb1.operate_user,
        tb1.check_user,
        tb1.sample_source,
        tb1.sample_type,
        tb1.sample_name,
        tb1.init_amount,
        tb2.cur_amount,
        tb1.create_time,
        tb1.modify_time,
        tb1.id,
        tb1.primary_project_id,
        tb1.primary_sample_id,
        tb1.unit
    FROM {table_one} as tb1
    LEFT JOIN {table_two} as tb2
    ON tb1.sample_id = tb2.sample_id
    '''.format(
        table_one=sample_db["name"],
        table_two=sample_stock_db["name"]
    )
    sample_base_info = db_conn.Query(sample_base_info_sql)
    db_conn.close_conn()
    return samples_total_number,sample_base_info